<?php
class Moutlet extends CI_Model {

    function __construct()
    {
        parent::__construct();
        $this->load->database();
    }
    function get_user_auto($q){
      $this->db->select('B.id_users, B.name, C.name as privilege');
      $this->db->like('LOWER(B.name)', strtolower($q));
      $this->db->or_like('LOWER(C.name)', strtolower($q));
      $this->db->join("m_users as B", "A.id_users = B.id_users", "left");
      $this->db->join("m_privilege as C", "A.id_privilege = C.id_privilege", "left");
      $this->db->group_by("A.id_users"); 
      $data = $this->db->get("d_user_privilege as A")->result();
      if(is_array($data)){
        foreach($data as $dt){
          $items[$dt->name." | ".$dt->privilege] = $dt->id_users;
        }
      }
      return $items;
    }
     function get_auto_outlet($q){
      $q = strtolower($q);
      $query = "
        SELECT *
        FROM m_outlet
        WHERE 
        LOWER(name) LIKE '%{$q}%'
        ";
//print $query;
      $data = $this->db->query($query)->result();
      $items = array();
      if(is_array($data)){
        foreach($data as $dt){
          $items[$dt->name] = $dt->id_privilege;
        }
      }
      return $items;
    }
  
    function get_auto_bc($q){
      $q = strtolower($q);
      $query = "
           select A.name
from d_user_privilege As B
left join m_users as A on A.id_users = B.id_users
where B.id_privilege=6 and
        LOWER(A.name) LIKE '%{$q}%'
        ";
     
//print $query;
      $data = $this->db->query($query)->result();
      $items = array();
      if(is_array($data)){
        foreach($data as $dt){
          $items[$dt->name] = $dt->id_privilege;
        }
      }
      return $items;
    }
    
    function ajax_get_user_auto($q){
      $q = strtolower($q);
      $query = "
        SELECT `A`.`id_pic_users`, `B`.`id_users`, `B`.`name`, `C`.`name` as privilege
FROM (`d_pic_users` as A)
LEFT JOIN `m_users` as B ON `A`.`id_users` = `B`.`id_users`
LEFT JOIN `d_user_privilege` as D ON `B`.`id_users` = `D`.`id_users`
LEFT JOIN `m_privilege` as C ON `D`.`id_privilege` = `C`.`id_privilege`
WHERE (LOWER(B.name)  LIKE '%{$q}%'
OR  LOWER(C.name)  LIKE '%{$q}%')
GROUP BY `A`.`id_users`
        ";
//print $query;
      $data = $this->db->query($query)->result();
      $items = array();
      if(is_array($data)){
        foreach($data as $dt){
          $items[$dt->name." | ".$dt->privilege] = $dt->id_pic_users;
        }
      }
      return $items;
    }
    
    function insert_user($kirim){
      $this->db->insert('m_users', $kirim);
      return $this->db->insert_id();
    }
    function update_user($id, $kirim){
      $this->db->where("id_users", $id);
      return $this->db->update("m_users", $kirim);
    }
    function get_detail($id){
      $this->db->where("id_users", $id);
      return $this->db->get("m_users")->row();
    }
    function change_status($id, $status){
      $this->db->where("id_users", $id);
      if($status == 1)
        $kirim['id_status_user'] = 2;
      else if($status == 2)
        $kirim['id_status_user'] = 1;
      else
        $kirim['id_status_user'] = $status;
      return $this->db->update("m_users", $kirim);
    }
    function get(){
      $data = $this->db->get("m_outlet")->result();
      
      return $data;
    }
    function get_milik($id = 0){
      $this->db->select("A.*");
      $this->db->join("d_outlet_users AS B", "A.id_outlet = B.id_outlet","left");
      if($this->nbscache->get_olahan("permission", $this->session->userdata("id_privilege"), "see_all_outlet", "add") !== FALSE AND $this->uri->segment(3) < 1){
        $id = 1;
      }
      if($id != 1){
        $this->db->where("B.id_users", $id);
       
      } 
      $this->db->where("A.id_status_outlet", "0");
      $this->db->group_by("A.id_outlet");
      $data = $this->db->get("m_outlet AS A")->result();
      return $data;
    }
    function pica_all_xls($parent,$data_export){
    //  $detail = $this->global_models->get_query("select * from m_actifity_report");
      
//      foreach ($parent as $ky => $prt){
//        $data[$ky] = $data_export[$ky];
//      }
      
      $bulan = array(
          "1" =>  "Januari",
          "2" =>  "Februari",
          "3" =>  "Maret",
          "4" =>  "April",
          "5" =>  "Mei",
          "6" =>  "Juni",
          "7" =>  "Juli",
          "8" =>  "Agustus",
          "9" =>  "September",
          "10" =>  "Oktober",
          "11" =>  "Nopember",
          "12" =>  "Desember",
      );
     // $outlet = $this->global_models->get_field("m_outlet", "name", array("id_outlet" => $detail[0]->id_outlet));
      $filename = "PICA-ALL";
      $objPHPExcel = $this->phpexcel;
      $objPHPExcel->getProperties()->setCreator("Mr Montir")
							 ->setLastModifiedBy("Mr Montir")
							 ->setTitle("Users Data")
							 ->setSubject("Users Data")
							 ->setDescription("Report users data.")
							 ->setKeywords("report users data")
							 ->setCategory("Users");

      $objPHPExcel->setActiveSheetIndex(0);
      
      $objPHPExcel->getActiveSheet()->mergeCells('A1:M1');
      $objPHPExcel->getActiveSheet()->setCellValue('A1', 'PICA ALL');
      $objPHPExcel->getActiveSheet()->mergeCells('A2:M2');
      $objPHPExcel->getActiveSheet()->setCellValue('A2', '(Problem Identification & Corrective Action)');
      $objPHPExcel->getActiveSheet()->getStyle('A1:M2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
      $objPHPExcel->getActiveSheet()->getStyle('A1:M2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
      $objPHPExcel->getActiveSheet()->getStyle('A1:M2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
      
      $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Outlet');
      $objPHPExcel->getActiveSheet()->setCellValue('C4', "MR MONTIR");
      /*$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Tanggal');
      $objPHPExcel->getActiveSheet()->setCellValue('C5', $bulan[$detail[0]->month]." ".$detail[0]->year);
      $objPHPExcel->getActiveSheet()->setCellValue('A6', 'Bulan');
      $objPHPExcel->getActiveSheet()->setCellValue('A7', 'Minggu ke'); */
      
      
      
      $objPHPExcel->getActiveSheet()->getStyle('A1:M7')->getFont()->setBold(true);
      $objPHPExcel->getActiveSheet()->getRowDimension(9)->setRowHeight(30);
      
      $objPHPExcel->getActiveSheet()->mergeCells('A6:G6');
      $objPHPExcel->getActiveSheet()->setCellValue('A6', 'Masalah');
      $objPHPExcel->getActiveSheet()->getStyle("A6:G6")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('A7', 'No');
      $objPHPExcel->getActiveSheet()->getStyle("A7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('B7', 'Tanggal');
      $objPHPExcel->getActiveSheet()->getStyle("B7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('C7', 'Kelompok');
      $objPHPExcel->getActiveSheet()->getStyle("C7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('D7', 'Outlet');
      $objPHPExcel->getActiveSheet()->getStyle("D7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('E7', 'Kategori');
      $objPHPExcel->getActiveSheet()->getStyle("E7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      $objPHPExcel->getActiveSheet()->setCellValue('F7', 'Sumber');
      $objPHPExcel->getActiveSheet()->getStyle("F7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      $objPHPExcel->getActiveSheet()->setCellValue('G7', 'Deskripsi');
      $objPHPExcel->getActiveSheet()->getStyle("G7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->mergeCells('H6:N6');
      $objPHPExcel->getActiveSheet()->setCellValue('H6', 'Selesai');
      $objPHPExcel->getActiveSheet()->getStyle("H6:N6")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('H7', 'Due Date');
      $objPHPExcel->getActiveSheet()->getStyle("H7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('I7', 'Tanggal');
      $objPHPExcel->getActiveSheet()->getStyle("I7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('J7', 'Tindakan');
      $objPHPExcel->getActiveSheet()->getStyle("J7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
     
      
      $objPHPExcel->getActiveSheet()->setCellValue('K7', 'Lama Laporan');
      $objPHPExcel->getActiveSheet()->getStyle("K7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('L7', 'Lama Pengerjaan');
      $objPHPExcel->getActiveSheet()->getStyle("L7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('M7', 'Status');
      $objPHPExcel->getActiveSheet()->getStyle("M7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('N7', 'PIC');
      $objPHPExcel->getActiveSheet()->getStyle("N7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      $kategori = array(
          1 => "SDM",
          2 => "Service",
          3 => "Administrasi",
          4 => "Promo",
          5 => "Tools",
          6 => "IT - Software & Hardware",
          7 => "Parts",
          9 => "Lainnya"
          );
      $sumber = array(
          1 => "Email",
          2 => "Telphone",
          3 => "Pesan Singkat",
      );
      $status = array(
          0 => "Draft",
          1 => "Progress",
          2 => "Finish"
      );
      if(is_array($data_export)){
        $no = 1;
        foreach($data_export as $key => $dt){
          $objPHPExcel->getActiveSheet()->setCellValue('A'.(7+$no), $no);
          $objPHPExcel->getActiveSheet()->getStyle("A".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('B'.(7+$no), $dt->tanggal_buat);
          $objPHPExcel->getActiveSheet()->getStyle("B".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('C'.(7+$no), $kategori[$dt->kategori]);
          $objPHPExcel->getActiveSheet()->getStyle("C".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('D'.(7+$no), $dt->outlet);
          $objPHPExcel->getActiveSheet()->getStyle("D".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('E'.(7+$no), $dt->kategori_pica);
          $objPHPExcel->getActiveSheet()->getStyle("E".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('F'.(7+$no), $sumber[$dt->sumber_info_view]);
          $objPHPExcel->getActiveSheet()->getStyle("F".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('G'.(7+$no), $dt->problem);
          $objPHPExcel->getActiveSheet()->getStyle("G".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('H'.(7+$no), $dt->due_date);
          $objPHPExcel->getActiveSheet()->getStyle("H".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('I'.(7+$no), $dt->tanggal_selesai);
          $objPHPExcel->getActiveSheet()->getStyle("I".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('J'.(7+$no), $dt->corrective);
          $objPHPExcel->getActiveSheet()->getStyle("J".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('K'.(7+$no), $lama_laporan);
          $objPHPExcel->getActiveSheet()->getStyle("K".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('L'.(7+$no), $lama_pengerjaan);
          $objPHPExcel->getActiveSheet()->getStyle("L".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('M'.(7+$no), $status[$dt->fix]);
          $objPHPExcel->getActiveSheet()->getStyle("M".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('N'.(7+$no), $dt->pic);
          $objPHPExcel->getActiveSheet()->getStyle("N".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          $no++;
        }
      }
      
      $objPHPExcel->getActiveSheet()->getStyle("A6:N".(5+$nok))->applyFromArray(
        array(
            'borders' => array(
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      
      $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(6);
      $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
      $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
      $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
      $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
      $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
      $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(35);
      $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
      $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
      $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(35);
      $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(13);
       $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(17);
       $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
       $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
      $objPHPExcel->getActiveSheet()->freezePane('A8');
      
      $objPHPExcel->getActiveSheet()->getStyle('G8:G'.(8+$no))->getAlignment()->setWrapText(true);
      $objPHPExcel->getActiveSheet()->getStyle('I8:I'.(8+$no))->getAlignment()->setWrapText(true);
      
      $objPHPExcel->setActiveSheetIndex(0);
      header('Content-Type: application/vnd.ms-excel');
      header('Content-Disposition: attachment;filename="'.$filename."-".date("Y m d").'"');
      header('Cache-Control: max-age=0');
      $objWriter = PHPExcel_IOFactory::createWriter($this->phpexcel, 'Excel5');
//$objWriter->save(str_replace('.php', '.xls', __FILE__));
      $objWriter->save('php://output');die;
      
    }
    
    function export_xls($filename){
      $objPHPExcel = $this->phpexcel;
      $objPHPExcel->getProperties()->setCreator("Mr Montir")
							 ->setLastModifiedBy("Mr Montir")
							 ->setTitle("Users Data")
							 ->setSubject("Users Data")
							 ->setDescription("Report users data.")
							 ->setKeywords("report users data")
							 ->setCategory("Users");

      $objPHPExcel->setActiveSheetIndex(0);
      
      $objPHPExcel->getActiveSheet()->mergeCells('A1:C2');
      $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Data Users');
      $objPHPExcel->getActiveSheet()->getStyle('A1:C2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
      $objPHPExcel->getActiveSheet()->getStyle('A1:C2')->getFill()->getStartColor()->setARGB('FF808080');
      $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
      $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
      $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
      
      $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Name');
      $objPHPExcel->getActiveSheet()->setCellValue('B4', 'Email');
      $objPHPExcel->getActiveSheet()->setCellValue('C4', 'Status');
      $objPHPExcel->getActiveSheet()->getStyle('A4:C4')->applyFromArray(
          array(
            'font'    => array(
              'bold'      => true
            ),
            'alignment' => array(
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
            ),
            'borders' => array(
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
            ),
            'fill' => array(
              'type'       => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
                'rotation'   => 90,
              'startcolor' => array(
                'argb' => 'FFA0A0A0'
              ),
              'endcolor'   => array(
                'argb' => 'FFFFFFFF'
              )
            )
          )
      );
      
      $data = $this->get();
      if(is_array($data)){
        foreach ($data as $key => $value) {
          $objPHPExcel->getActiveSheet()->setCellValue('A'.(5+$key), $value->name);
          $objPHPExcel->getActiveSheet()->setCellValue('B'.(5+$key), $value->email);
          $objPHPExcel->getActiveSheet()->setCellValue('C'.(5+$key), $value->status);
        }
      }
      $objPHPExcel->getActiveSheet()->getStyle('A5:C'.(5+$key))->applyFromArray(
          array(
            'font'    => array(
              'bold'      => false
            ),
            'alignment' => array(
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
            ),
            'borders' => array(
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
            ),
            'fill' => array(
              'type'       => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
                'rotation'   => 90,
              'startcolor' => array(
                'argb' => 'FFA0A0A0'
              ),
              'endcolor'   => array(
                'argb' => 'FFFFFFFF'
              )
            )
          )
      );
      $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
      $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
      $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
//      $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(30);
//      $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(50);
//      $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
      
      $objPHPExcel->setActiveSheetIndex(0);
      header('Content-Type: application/vnd.ms-excel');
      header('Content-Disposition: attachment;filename="'.$filename."-".date("Y m d").'"');
      header('Cache-Control: max-age=0');
      $objWriter = PHPExcel_IOFactory::createWriter($this->phpexcel, 'Excel5');
//$objWriter->save(str_replace('.php', '.xls', __FILE__));
      $objWriter->save('php://output');die;
    }
    function export_master_outlet(){
      $objPHPExcel = $this->phpexcel;
      $objPHPExcel->getProperties()->setCreator("Mr Montir")
							 ->setLastModifiedBy("Mr Montir")
							 ->setTitle("Users Data")
							 ->setSubject("Users Data")
							 ->setDescription("Report users data.")
							 ->setKeywords("report users data")
							 ->setCategory("Users");

      $objPHPExcel->setActiveSheetIndex(0);
      
      $objPHPExcel->getActiveSheet()->mergeCells('A1:E2');
      $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Data Users Outlet');
      $objPHPExcel->getActiveSheet()->getStyle('A1:E2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
      $objPHPExcel->getActiveSheet()->getStyle('A1:E2')->getFill()->getStartColor()->setARGB('FF808080');
      $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
      $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
      $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
      
      $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Outlet');
      $objPHPExcel->getActiveSheet()->setCellValue('B4', 'Kepala');
      $objPHPExcel->getActiveSheet()->setCellValue('C4', 'Password');
      $objPHPExcel->getActiveSheet()->setCellValue('D4', 'Owner');
      $objPHPExcel->getActiveSheet()->setCellValue('E4', 'Password');
      $objPHPExcel->getActiveSheet()->getStyle('A4:E4')->applyFromArray(
          array(
            'font'    => array(
              'bold'      => true
            ),
            'alignment' => array(
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
            ),
            'borders' => array(
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
            ),
            'fill' => array(
              'type'       => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
                'rotation'   => 90,
              'startcolor' => array(
                'argb' => 'FFA0A0A0'
              ),
              'endcolor'   => array(
                'argb' => 'FFFFFFFF'
              )
            )
          )
      );
      $data = $this->get_master_outlet();
      if(is_array($data)){
        foreach ($data as $key => $value) {
          $head_name = "";
          $head_link = "";
          $cl = 0;
          foreach($value->kepala as $km => $head){
            if($cl > 0){
              $head_name .= "\n";
              $head_link .= "\n";
            }
            $head_name .= "{$head->name}";
            $head_link .= "{$this->encrypt->decode($head->pass)}";
            $cl++;
          }
          
          $own_name = "";
          $own_link = "";
          $cj = 0;
          foreach($value->owner as $km => $own){
            if($cj > 0){
              $own_name .= "\n";
              $own_link .= "\n";
            }
            $own_name .= "{$own->name}";
            $own_link .= "{$this->encrypt->decode($own->pass)}";
            $cj++;
          }
          
          $objPHPExcel->getActiveSheet()->setCellValue('A'.(5+$key), $value->name);
          
          $objPHPExcel->getActiveSheet()->setCellValue('B'.(5+$key), $head_name);
          $objPHPExcel->getActiveSheet()->setCellValue('C'.(5+$key), $head_link);
          $objPHPExcel->getActiveSheet()->getStyle('B'.(5+$key))->getAlignment()->setWrapText(true);
          $objPHPExcel->getActiveSheet()->getStyle('C'.(5+$key))->getAlignment()->setWrapText(true);
            
          $objPHPExcel->getActiveSheet()->setCellValue('D'.(5+$key), $own_name);
          $objPHPExcel->getActiveSheet()->setCellValue('E'.(5+$key), $own_link);
          $objPHPExcel->getActiveSheet()->getStyle('D'.(5+$key))->getAlignment()->setWrapText(true);
          $objPHPExcel->getActiveSheet()->getStyle('E'.(5+$key))->getAlignment()->setWrapText(true);
        }
      }
      $objPHPExcel->getActiveSheet()->getStyle('A5:E'.(5+$key))->applyFromArray(
          array(
            'font'    => array(
              'bold'      => false
            ),
            'alignment' => array(
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
            ),
            'borders' => array(
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
            ),
            'fill' => array(
              'type'       => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
                'rotation'   => 90,
              'startcolor' => array(
                'argb' => 'FFA0A0A0'
              ),
              'endcolor'   => array(
                'argb' => 'FFFFFFFF'
              )
            )
          )
      );
      $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
      $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
      $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
      $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
      $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
//      $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(30);
//      $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(50);
//      $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
      
      $objPHPExcel->setActiveSheetIndex(0);
      header('Content-Type: application/vnd.ms-excel');
      header('Content-Disposition: attachment;filename="Master-'.date("Y m d").'"');
      header('Cache-Control: max-age=0');
      $objWriter = PHPExcel_IOFactory::createWriter($this->phpexcel, 'Excel5');
//$objWriter->save(str_replace('.php', '.xls', __FILE__));
      $objWriter->save('php://output');die;
    }
    function insert_user_outlet($id_outlet, $kirim){
      $relasi = $this->global_models->get_array("id_users", "d_outlet_users", 
              array("id_outlet" => $id_outlet));
      $array_del = array_diff($relasi, $kirim);
      if(is_array($array_del)){
        foreach($array_del as $ad){
          $this->global_models->delete("d_outlet_users", array("id_outlet" => $id_outlet, "id_users" => $ad));
        }
      }
      if(is_array($kirim)){
        foreach($kirim as $krm){
          $cek = $this->global_models->get_field("d_outlet_users", "id_outlet", 
                  array("id_outlet"  =>  $id_outlet, "id_users" => $krm));
          if($cek === FALSE){
            $this->global_models->insert("d_outlet_users", 
                    array(
                        "id_outlet" => $id_outlet, 
                        "id_users" => $krm,
                        "create_by_users" =>  $this->session->userdata('id'),
                        "create_date" =>  date("Y-m-d H:i:s"),
                        "update_by_users" =>  $this->session->userdata('id'),
                        ));
          }
          else{
            $this->global_models->update("d_outlet_users", 
                    array(
                        "id_outlet" =>  $id_outlet,
                        "id_users"  =>  $krm
                    ),
                    array(
                        "update_by_users" =>  $this->session->userdata('id'),
                        ));
          }
        }
        return true;
      }
      return false;
    }
    function insert_user_pic($id_outlet, $kirim){
      $relasi = $this->global_models->get_array("id_users", "d_pic_users", 
              array("id_outlet" => $id_outlet));
      $array_del = array_diff($relasi, $kirim);
      if(is_array($array_del)){
        foreach($array_del as $ad){
          $this->global_models->delete("d_pic_users", array("id_outlet" => $id_outlet, "id_users" => $ad));
        }
      }
      if(is_array($kirim)){
        foreach($kirim as $krm){
          $cek = $this->global_models->get_field("d_pic_users", "id_outlet", 
                  array("id_outlet"  =>  $id_outlet, "id_users" => $krm));
          if($cek === FALSE){
            $this->global_models->insert("d_pic_users", 
                    array(
                        "id_outlet" => $id_outlet, 
                        "id_users" => $krm,
                        "create_by_users" =>  $this->session->userdata('id'),
                        "create_date" =>  date("Y-m-d H:i:s"),
                        "update_by_users" =>  $this->session->userdata('id'),
                        ));
          }
          else{
            $this->global_models->update("d_pic_users", 
                    array(
                        "id_outlet" =>  $id_outlet,
                        "id_users"  =>  $krm
                    ),
                    array(
                        "update_by_users" =>  $this->session->userdata('id'),
                        ));
          }
        }
        return true;
      }
      return false;
    }
    function get_bc(){
      $this->db->select("A.*");
      $this->db->join("d_user_privilege as B", "A.id_users = B.id_users", 'left');
      $this->db->join("m_privilege as C", "B.id_privilege = C.id_privilege", "left");
      $this->db->group_by("A.id_users");
      $this->db->where("C.name", "Operasional - BC");
      $this->db->where("A.id_status_user", 1);
      return $this->db->get("m_users as A")->result();
    }
    function get_pica(){
      $this->db->select("A.*");
      $this->db->join("m_privilege as B", "B.id_privilege = A.id_privilege", "left");
    
      return $this->db->get("d_pica_privilege as A")->result();
    }
    function get_master_outlet(){
      $this->db->select("*");
      $this->db->where("id_status_outlet", "0");
      $outlet = $this->db->get("m_outlet")->result();
      foreach ($outlet as $key => $value) {
        $this->db->select("A.*, D.name AS privilege");
        $this->db->join("d_outlet_users AS B", "A.id_users = B.id_users AND B.id_outlet = {$value->id_outlet}", "inner");
        $this->db->join("d_user_privilege AS C", "A.id_users = C.id_users", "left");
        $this->db->join("m_privilege AS D", "D.id_privilege = C.id_privilege", "left");
//        $this->db->where("B.id_outlet", $value->id_outlet);
        $this->db->where("D.name", "Outlet - Kepala");
        $this->db->or_where("D.name", "Outlet - Owner");
        $this->db->group_by("A.id_users");
        $user = $this->db->get("m_users AS A")->result();
        foreach($user as $ky => $vl){
          if($vl->privilege == "Outlet - Kepala"){
            $outlet[$key]->kepala[] = $vl;
            $outlet[$key]->id_kepala[] = $vl->id_users;
//            $outlet[$key]->owner[] = "";
//            $outlet[$key]->id_owner[] = "";
          }
          else{
//            $outlet[$key]->kepala[] = "";
//            $outlet[$key]->id_kepala[] = "";
            $outlet[$key]->owner[] = $vl;
            $outlet[$key]->id_owner[] = $vl->id_users;
          }
        }
      }
      return $outlet;
    }
    
    function pica_report_xls(){
    
      $filename = "Report-PICA";
      $objPHPExcel = $this->phpexcel;
      $objPHPExcel->getProperties()->setCreator("Mr Montir")
							 ->setLastModifiedBy("Mr Montir")
							 ->setTitle("Users Data")
							 ->setSubject("Users Data")
							 ->setDescription("Report users data.")
							 ->setKeywords("report users data")
							 ->setCategory("Users");
      
      $awal_bulan = date("Y-m-")."01";
      
      $data_export_create = $this->db->query("
         SELECT A.*, B.name as outlet, C.name AS pic, D.name AS kategori_pica
         FROM d_history AS A
         LEFT JOIN m_outlet AS B ON A.id_outlet = B.id_outlet
         LEFT JOIN m_pic_pica AS C ON A.id_pic_pica = C.id_pic_pica
         LEFT JOIN m_kategori_pica AS D ON A.id_kategori_pica = D.id_kategori_pica
         WHERE A.tanggal_buat BETWEEN '{$awal_bulan}' AND '".date("Y-m-d")."'
         ORDER BY A.tanggal_buat DESC
         ")->result();
      $data_export_progress = $this->db->query("
         SELECT A.*, B.name as outlet, C.name AS pic, D.name AS kategori_pica
         FROM d_history AS A
         LEFT JOIN m_outlet AS B ON A.id_outlet = B.id_outlet
         LEFT JOIN m_pic_pica AS C ON A.id_pic_pica = C.id_pic_pica
         LEFT JOIN m_kategori_pica AS D ON A.id_kategori_pica = D.id_kategori_pica
         WHERE A.fix = 1
         ORDER BY A.due_date ASC
         ")->result();
      $data_export_finish = $this->db->query("
         SELECT A.*, B.name as outlet, C.name AS pic, D.name AS kategori_pica
         FROM d_history AS A
         LEFT JOIN m_outlet AS B ON A.id_outlet = B.id_outlet
         LEFT JOIN m_pic_pica AS C ON A.id_pic_pica = C.id_pic_pica
         LEFT JOIN m_kategori_pica AS D ON A.id_kategori_pica = D.id_kategori_pica
         WHERE A.fix = 2 AND A.tanggal_selesai BETWEEN '{$awal_bulan}' AND '".date("Y-m-d")."'
         ORDER BY A.tanggal_selesai DESC
         ")->result();
      $data_export_due_date = $this->db->query("
         SELECT A.*, B.name as outlet, C.name AS pic, D.name AS kategori_pica
         FROM d_history AS A
         LEFT JOIN m_outlet AS B ON A.id_outlet = B.id_outlet
         LEFT JOIN m_pic_pica AS C ON A.id_pic_pica = C.id_pic_pica
         LEFT JOIN m_kategori_pica AS D ON A.id_kategori_pica = D.id_kategori_pica
         WHERE A.fix = 1 AND A.due_date BETWEEN '{$awal_bulan}' AND '".date("Y-m-d")."'
         ORDER BY A.due_date ASC
         ")->result();
      $data_export_over_due = $this->db->query("
         SELECT A.*, B.name as outlet, C.name AS pic, D.name AS kategori_pica
         FROM d_history AS A
         LEFT JOIN m_outlet AS B ON A.id_outlet = B.id_outlet
         LEFT JOIN m_pic_pica AS C ON A.id_pic_pica = C.id_pic_pica
         LEFT JOIN m_kategori_pica AS D ON A.id_kategori_pica = D.id_kategori_pica
         WHERE A.fix = 1 AND A.due_date <= '".date("Y-m-d")."'
         ORDER BY A.due_date ASC
         ")->result();
         
      $array = array(
          "Detail PICA Create"      => $data_export_create,
          "Detail PICA Progress"    => $data_export_progress,
          "Detail PICA Finish"      => $data_export_finish,
          "Detail PICA Due Date"    => $data_export_due_date,
          "Detail PICA Over Due"    => $data_export_over_due,
      );
      $c_i = 0;
foreach($array AS $title_tab => $data_export){
  $objPHPExcel->createSheet();
      $objPHPExcel->setActiveSheetIndex($c_i);
      $objPHPExcel->getActiveSheet()->setTitle($title_tab);
      
      $objPHPExcel->getActiveSheet()->mergeCells('A1:M1');
      $objPHPExcel->getActiveSheet()->setCellValue('A1', $title_tab);
      $objPHPExcel->getActiveSheet()->mergeCells('A2:M2');
      $objPHPExcel->getActiveSheet()->setCellValue('A2', '(Problem Identification & Corrective Action)');
      $objPHPExcel->getActiveSheet()->getStyle('A1:M2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
      $objPHPExcel->getActiveSheet()->getStyle('A1:M2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
      $objPHPExcel->getActiveSheet()->getStyle('A1:M2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
      
//      $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Outlet');
//      $objPHPExcel->getActiveSheet()->setCellValue('C4', "MR MONTIR");
      /*$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Tanggal');
      $objPHPExcel->getActiveSheet()->setCellValue('C5', $bulan[$detail[0]->month]." ".$detail[0]->year);
      $objPHPExcel->getActiveSheet()->setCellValue('A6', 'Bulan');
      $objPHPExcel->getActiveSheet()->setCellValue('A7', 'Minggu ke'); */
      
      
      
      $objPHPExcel->getActiveSheet()->getStyle('A1:M7')->getFont()->setBold(true);
      $objPHPExcel->getActiveSheet()->getRowDimension(9)->setRowHeight(30);
      
      $objPHPExcel->getActiveSheet()->mergeCells('A6:G6');
      $objPHPExcel->getActiveSheet()->setCellValue('A6', 'Masalah');
      $objPHPExcel->getActiveSheet()->getStyle("A6:G6")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('A7', 'No');
      $objPHPExcel->getActiveSheet()->getStyle("A7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('B7', 'Tanggal');
      $objPHPExcel->getActiveSheet()->getStyle("B7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('C7', 'Kelompok');
      $objPHPExcel->getActiveSheet()->getStyle("C7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('D7', 'Outlet');
      $objPHPExcel->getActiveSheet()->getStyle("D7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('E7', 'Kategori');
      $objPHPExcel->getActiveSheet()->getStyle("E7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      $objPHPExcel->getActiveSheet()->setCellValue('F7', 'Sumber');
      $objPHPExcel->getActiveSheet()->getStyle("F7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      $objPHPExcel->getActiveSheet()->setCellValue('G7', 'Deskripsi');
      $objPHPExcel->getActiveSheet()->getStyle("G7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->mergeCells('H6:N6');
      $objPHPExcel->getActiveSheet()->setCellValue('H6', 'Selesai');
      $objPHPExcel->getActiveSheet()->getStyle("H6:N6")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('H7', 'Due Date');
      $objPHPExcel->getActiveSheet()->getStyle("H7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('I7', 'Tanggal');
      $objPHPExcel->getActiveSheet()->getStyle("I7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('J7', 'Tindakan');
      $objPHPExcel->getActiveSheet()->getStyle("J7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
     
      
      $objPHPExcel->getActiveSheet()->setCellValue('K7', 'Lama Laporan');
      $objPHPExcel->getActiveSheet()->getStyle("K7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('L7', 'Lama Pengerjaan');
      $objPHPExcel->getActiveSheet()->getStyle("L7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('M7', 'Status');
      $objPHPExcel->getActiveSheet()->getStyle("M7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('N7', 'PIC');
      $objPHPExcel->getActiveSheet()->getStyle("N7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      $kategori = array(
          1 => "SDM",
          2 => "Service",
          3 => "Administrasi",
          4 => "Promo",
          5 => "Tools",
          6 => "IT - Software & Hardware",
          7 => "Parts",
          9 => "Lainnya"
          );
      $sumber = array(
          1 => "Email",
          2 => "Telphone",
          3 => "Pesan Singkat",
      );
      $status = array(
          0 => "Draft",
          1 => "Progress",
          2 => "Finish"
      );
      if(is_array($data_export)){
        $no = 1;
        foreach($data_export as $key => $dt){
          $objPHPExcel->getActiveSheet()->setCellValue('A'.(7+$no), $no);
          $objPHPExcel->getActiveSheet()->getStyle("A".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('B'.(7+$no), $dt->tanggal_buat);
          $objPHPExcel->getActiveSheet()->getStyle("B".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('C'.(7+$no), $kategori[$dt->kategori]);
          $objPHPExcel->getActiveSheet()->getStyle("C".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('D'.(7+$no), $dt->outlet);
          $objPHPExcel->getActiveSheet()->getStyle("D".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('E'.(7+$no), $dt->kategori_pica);
          $objPHPExcel->getActiveSheet()->getStyle("E".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('F'.(7+$no), $sumber[$dt->sumber_info_view]);
          $objPHPExcel->getActiveSheet()->getStyle("F".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('G'.(7+$no), $dt->problem);
          $objPHPExcel->getActiveSheet()->getStyle("G".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('H'.(7+$no), $dt->due_date);
          $objPHPExcel->getActiveSheet()->getStyle("H".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('I'.(7+$no), $dt->tanggal_selesai);
          $objPHPExcel->getActiveSheet()->getStyle("I".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('J'.(7+$no), $dt->corrective);
          $objPHPExcel->getActiveSheet()->getStyle("J".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('K'.(7+$no), $lama_laporan);
          $objPHPExcel->getActiveSheet()->getStyle("K".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('L'.(7+$no), $lama_pengerjaan);
          $objPHPExcel->getActiveSheet()->getStyle("L".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('M'.(7+$no), $status[$dt->fix]);
          $objPHPExcel->getActiveSheet()->getStyle("M".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          $objPHPExcel->getActiveSheet()->setCellValue('N'.(7+$no), $dt->pic);
          $objPHPExcel->getActiveSheet()->getStyle("N".(7+$no))->applyFromArray(
            array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_TOP,
                ),
                'borders' => array(
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                  ),
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          
          if($dt->tanggal_buat)
            $objPHPExcel->getActiveSheet()->setCellValue('O'.(7+$no), strtotime($dt->tanggal_buat));
          if($dt->tanggal_selesai)
            $objPHPExcel->getActiveSheet()->setCellValue('P'.(7+$no), strtotime($dt->tanggal_selesai));
          if($dt->due_date)
            $objPHPExcel->getActiveSheet()->setCellValue('Q'.(7+$no), strtotime($dt->due_date));
          
          $no++;
        }
      }
      
      $objPHPExcel->getActiveSheet()->getStyle("A6:N".(5+$nok))->applyFromArray(
        array(
            'borders' => array(
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setVisible(false);
      $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setVisible(false);
      $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setVisible(false);
      
      $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(6);
      $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
      $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
      $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
      $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
      $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
      $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(35);
      $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
      $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
      $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(35);
      $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(13);
       $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(17);
       $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
       $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
      $objPHPExcel->getActiveSheet()->freezePane('A8');
      
      $objPHPExcel->getActiveSheet()->getStyle('G8:G'.(8+$no))->getAlignment()->setWrapText(true);
      $objPHPExcel->getActiveSheet()->getStyle('J8:J'.(8+$no))->getAlignment()->setWrapText(true);
      $objPHPExcel->getActiveSheet()->getStyle('D8:D'.(8+$no))->getAlignment()->setWrapText(true);
      $objPHPExcel->getActiveSheet()->getStyle('E8:E'.(8+$no))->getAlignment()->setWrapText(true);
      $objPHPExcel->getActiveSheet()->getStyle('C8:C'.(8+$no))->getAlignment()->setWrapText(true);
      
      $objPHPExcel->setActiveSheetIndex($c_i);
 $c_i++;     
}
      $colomn = "B";
      for($day = 1 ; $day <= date("t") ; $day++){
        
        $jumlah_create = $this->db->query("
          SELECT count(A.id_history) AS jml, B.id_kategori_pica, B.name
          FROM m_kategori_pica AS B
          LEFT JOIN d_history AS A ON (A.id_kategori_pica = B.id_kategori_pica AND A.tanggal_buat = '".date("Y-m-").$day."')
          GROUP BY B.id_kategori_pica
          ORDER BY B.kategori ASC, B.id_kategori_pica ASC
          ")->result();
//        $jumlah_create = $this->db->query("
//          SELECT count(A.id_history) AS jml
//          FROM d_history AS A
//          JOIN m_kategori_pica AS B ON A.id_kategori_pica = B.id_kategori_pica
//          WHERE A.tanggal_buat = '".date("Y-m-").$day."'
//          GROUP BY B.id_kategori_pica
//          ")->row();
        
        $jumlah_progress = $this->db->query("
          SELECT count(A.id_history) AS jml, B.id_kategori_pica, B.name
          FROM m_kategori_pica AS B
          LEFT JOIN d_history AS A ON (A.id_kategori_pica = B.id_kategori_pica AND (A.fix = 1 OR (A.fix = 2 AND A.tanggal_selesai > '".date("Y-m-").$day."')))
          GROUP BY B.id_kategori_pica
          ORDER BY B.kategori ASC, B.id_kategori_pica ASC
          ")->result();
        
        $jumlah_finish = $this->db->query("
          SELECT count(A.id_history) AS jml, B.id_kategori_pica, B.name
          FROM m_kategori_pica AS B
          LEFT JOIN d_history AS A ON (A.id_kategori_pica = B.id_kategori_pica AND A.fix = 2 AND A.tanggal_selesai = '".date("Y-m-").$day."')
          GROUP BY B.id_kategori_pica
          ORDER BY B.kategori ASC, B.id_kategori_pica ASC
          ")->result();
        
        $jumlah_due_date = $this->db->query("
          SELECT count(A.id_history) AS jml, B.id_kategori_pica, B.name
          FROM m_kategori_pica AS B
          LEFT JOIN d_history AS A ON (A.id_kategori_pica = B.id_kategori_pica AND A.fix = 1 AND A.due_date = '".date("Y-m-").$day."')
          GROUP BY B.id_kategori_pica
          ORDER BY B.kategori ASC, B.id_kategori_pica ASC
          ")->result();
        $jumlah_over_due = $this->db->query("
          SELECT count(A.id_history) AS jml, B.id_kategori_pica, B.name
          FROM m_kategori_pica AS B
          LEFT JOIN d_history AS A ON (A.id_kategori_pica = B.id_kategori_pica AND A.fix = 1 AND A.due_date <= '".date("Y-m-").$day."')
          GROUP BY B.id_kategori_pica
          ORDER BY B.kategori ASC, B.id_kategori_pica ASC
          ")->result();
        
        $sum_create[$day] = $jumlah_create;
        $sum_progress[$day] = $jumlah_progress;
        $sum_finish[$day] = $jumlah_finish;
        $sum_due_date[$day] = $jumlah_due_date;
        $sum_over_due[$day] = $jumlah_over_due;
        
      }

      $array_table = array(
          "Summary PICA Create"     => $sum_create,
          "Summary PICA Progress"   => $sum_progress,
          "Summary PICA Finish"     => $sum_finish,
          "Summary PICA Due Date"   => $sum_due_date,
          "Summary PICA Over Due"   => $sum_over_due,
      );
//      print "<pre>";
//      print_r($array_table["Summary PICA Progress"]);
//      print "</pre>";die;
//      $kategori_pica = $this->db->query("
//        SELECT *
//        FROM m_kategori_pica
//        ORDER BY kategori ASC
//        ")->result();
      $c_l = 5;
foreach($array_table AS $title_tab => $data_export_sum){
  $objPHPExcel->createSheet();
      $objPHPExcel->setActiveSheetIndex($c_l);
      $objPHPExcel->getActiveSheet()->setTitle($title_tab);
      
      $colomn = "B";
      for($day = 1 ; $day <= date("t") ; $day++){
        $objPHPExcel->getActiveSheet()->setCellValue($colomn.'5', strtotime(date("Y-m-").$day));
        $objPHPExcel->getActiveSheet()->setCellValue($colomn.'6', $day);
        $objPHPExcel->getActiveSheet()->getColumnDimension($colomn)->setWidth(4);
        $objPHPExcel->getActiveSheet()->getStyle($colomn."6")->applyFromArray(
            array(
              'alignment' => array(
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
              ),
              'borders' => array(
                'left'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
                'right'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
                'top'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
                'bottom'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
              ),
            )
        );
        $colomn++;
      }
      if($title_tab != "Summary PICA Progress" AND $title_tab != "Summary PICA Over Due"){
        $objPHPExcel->getActiveSheet()->setCellValue($colomn.'6', "TOTAL");
        $objPHPExcel->getActiveSheet()->getStyle($colomn."6")->applyFromArray(
            array(
              'alignment' => array(
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
              ),
              'borders' => array(
                'left'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
                'right'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
                'top'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
                'bottom'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
              ),
            )
        );
      }
      
      $objPHPExcel->getActiveSheet()->mergeCells("A1:{$colomn}1");
      $objPHPExcel->getActiveSheet()->setCellValue('A1', $title_tab);
      $objPHPExcel->getActiveSheet()->mergeCells("A2:{$colomn}2");
//      $objPHPExcel->getActiveSheet()->setCellValue('A2', '(Problem Identification & Corrective Action)');
      $objPHPExcel->getActiveSheet()->getStyle("A1:{$colomn}2")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
      $objPHPExcel->getActiveSheet()->getStyle("A1:{$colomn}2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
      $objPHPExcel->getActiveSheet()->getStyle("A1:{$colomn}2")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
      
      $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(35);
//      $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Outlet');
//      $objPHPExcel->getActiveSheet()->setCellValue('C4', "MR MONTIR");
      /*$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Tanggal');
      $objPHPExcel->getActiveSheet()->setCellValue('C5', $bulan[$detail[0]->month]." ".$detail[0]->year);
      $objPHPExcel->getActiveSheet()->setCellValue('A6', 'Bulan');
      $objPHPExcel->getActiveSheet()->setCellValue('A7', 'Minggu ke'); */
      
      
      
      $objPHPExcel->getActiveSheet()->getStyle("A1:{$colomn}6")->getFont()->setBold(true);
//      $objPHPExcel->getActiveSheet()->getRowDimension(9)->setRowHeight(30);
      
      $objPHPExcel->getActiveSheet()->setCellValue('A6', 'Kategori');
      $objPHPExcel->getActiveSheet()->getStyle("A6")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      $objPHPExcel->getActiveSheet()->freezePane('A7');
      
      $row_ke = 7;
      foreach($data_export_sum[1] AS $kate_yg => $kp){
        $objPHPExcel->getActiveSheet()->setCellValue("A{$row_ke}", $kp->name);
        $objPHPExcel->getActiveSheet()->getStyle("A{$row_ke}")->applyFromArray(
            array(
              'alignment' => array(
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
//                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
              ),
              'borders' => array(
                'left'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
                'right'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
                'top'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
                'bottom'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
              ),
            )
        );
        
        $colomn = "B";
//        for($day = 1 ; $day <= date("t") ; $day++){
//        print "<pre>";
//        print_r($data_export);die;
        foreach($data_export_sum AS $day => $de){
          if($day <= date("d")){
            $objPHPExcel->getActiveSheet()->setCellValue("{$colomn}{$row_ke}", $de[$kate_yg]->jml);
            if($de[$kate_yg]->jml > 0){
              $objPHPExcel->getActiveSheet()->getStyle("{$colomn}{$row_ke}")->applyFromArray(
                array(
                    'fill' 	=> array(
                        'type'		=> PHPExcel_Style_Fill::FILL_SOLID,
                        'color'		=> array('argb' => 'FF00FF00')
                    )
                  )
              );
            }
          }
          $objPHPExcel->getActiveSheet()->getStyle("{$colomn}{$row_ke}")->applyFromArray(
              array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                ),
                'borders' => array(
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'bottom'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
          $colomn_temp = $colomn;
          $colomn++;
        }
        if($title_tab != "Summary PICA Progress" AND $title_tab != "Summary PICA Over Due"){
          $objPHPExcel->getActiveSheet()->setCellValue("{$colomn}{$row_ke}", "=SUM(B{$row_ke}:{$colomn_temp}{$row_ke})");
          $objPHPExcel->getActiveSheet()->getStyle("{$colomn}{$row_ke}")->applyFromArray(
              array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                ),
                'borders' => array(
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                  ),
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'bottom'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                ),
              )
          );
        }
        
        $row_ke++;
      }
      $objPHPExcel->getActiveSheet()->getStyle('A7:A'.$row_ke)->getAlignment()->setWrapText(true);
      
      if($title_tab != "Summary PICA Progress"){
        $objPHPExcel->getActiveSheet()->getStyle("A{$row_ke}:{$colomn}{$row_ke}")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->setCellValue("A".$row_ke, "TOTAL");
        $objPHPExcel->getActiveSheet()->getStyle("A".$row_ke)->applyFromArray(
            array(
              'alignment' => array(
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                  'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
              ),
              'borders' => array(
                'left'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
                'right'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
                'top'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
                'bottom'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
              ),
            )
        );
      }
      
      if($title_tab != "Summary PICA Progress"){
        $colomn = "B";
        for($day = 1 ; $day <= date("t") ; $day++){
          $objPHPExcel->getActiveSheet()->setCellValue($colomn.$row_ke, "=SUM({$colomn}7:{$colomn}".($row_ke-1).")");
          $objPHPExcel->getActiveSheet()->getStyle($colomn.$row_ke)->applyFromArray(
              array(
                'alignment' => array(
                  'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
  //                  'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                ),
                'borders' => array(
                  'left'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'right'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                  ),
                  'top'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                  ),
                  'bottom'     => array(
                    'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                  ),
                ),
              )
          );
          $colomn++;
        }
      
        $objPHPExcel->getActiveSheet()->setCellValue($colomn.$row_ke, "=SUM({$colomn}7:{$colomn}".($row_ke-1).")");
        $objPHPExcel->getActiveSheet()->getStyle("{$colomn}7:{$colomn}".($row_ke-1))->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle($colomn.$row_ke)->applyFromArray(
            array(
              'alignment' => array(
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
  //                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
              ),
              'borders' => array(
                'left'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
                'right'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
                'top'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
                'bottom'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
              ),
            )
        );
      }
      
      $objPHPExcel->getActiveSheet()->getRowDimension(5)->setVisible(false);
      $objPHPExcel->setActiveSheetIndex($c_l);
 $c_l++;     
}

      header('Content-Type: application/vnd.ms-excel');
      header('Content-Disposition: attachment;filename="'.$filename."-".date("Y m d").'"');
      header('Cache-Control: max-age=0');
      $objWriter = PHPExcel_IOFactory::createWriter($this->phpexcel, 'Excel5');
//$objWriter->save(str_replace('.php', '.xls', __FILE__));
      $objWriter->save('php://output');die;
      
    }
    
    function pica_rekap_xls(){
    
      $filename = "Rekap-PICA";
      $objPHPExcel = $this->phpexcel;
      $objPHPExcel->getProperties()->setCreator("Mr Montir")
							 ->setLastModifiedBy("Mr Montir")
							 ->setTitle("Users Data")
							 ->setSubject("Users Data")
							 ->setDescription("Report users data.")
							 ->setKeywords("report users data")
							 ->setCategory("Users");
      
      $awal_bulan = date("Y-m-")."01";
      
      $data_over_due_pic = $this->db->query("
        SELECT COUNT( A.id_history ) AS pic_pica, B.name
        FROM m_pic_pica AS B
        LEFT JOIN d_history AS A ON ( A.id_pic_pica = B.id_pic_pica
        AND A.fix =1
        AND A.due_date <=  '".date("Y-m-d")."' ) 
        WHERE B.name IS NOT NULL 
        GROUP BY B.name
        ")->result();
      
      $data_progress_pic = $this->db->query("
        SELECT COUNT( A.id_history ) AS pic_pica, B.name
        FROM m_pic_pica AS B
        LEFT JOIN d_history AS A ON (A.id_pic_pica = B.id_pic_pica
        AND A.fix = 1
        )
        WHERE B.name IS NOT NULL
        GROUP BY B.name
        ")->result();
      
      $data_over_cate = $this->db->query("
        SELECT COUNT( A.id_history ) AS pic_pica, B.name
        FROM m_kategori_pica AS B
        LEFT JOIN d_history AS A ON (A.id_kategori_pica = B.id_kategori_pica
        AND A.due_date <=  '".date("Y-m-d")."' AND A.fix = 1
        )
        GROUP BY B.name
        ")->result();
      
      $data_progress_cate = $this->db->query("
        SELECT COUNT( A.id_history ) AS pic_pica, B.name
        FROM m_kategori_pica AS B
        LEFT JOIN d_history AS A ON (A.id_kategori_pica = B.id_kategori_pica
        AND A.fix = 1
        )
        GROUP BY B.name
        ")->result();
      
      $data_over_outlet = $this->db->query("
        SELECT COUNT( A.id_history ) AS pic_pica, B.name
        FROM m_outlet AS B
        LEFT JOIN d_history AS A ON (A.id_outlet = B.id_outlet
        AND A.due_date <=  '".date("Y-m-d")."' AND A.fix = 1
        )
        WHERE B.id_status_outlet = 0
        GROUP BY B.name
        ")->result();
      
      $data_progress_outlet = $this->db->query("
        SELECT COUNT( A.id_history ) AS pic_pica, B.name
        FROM m_outlet AS B
        LEFT JOIN d_history AS A ON (A.id_outlet = B.id_outlet
        AND A.fix = 1
        )
        WHERE B.id_status_outlet = 0
        GROUP BY B.name
        ")->result();
      
      $array = array(
          "PIC & Status"     => array(
              "title"     => "PIC",
              "status"    => "Over Due",
              "status1"   => "Progress",
              "data"      => $data_over_due_pic,
              "data1"     => $data_progress_pic,
          ),
          "Kategory & Status"     => array(
              "title"     => "Kategory",
              "status"    => "Over Due",
              "data"      => $data_over_cate,
              "status1"   => "Progress",
              "data1"     => $data_progress_cate,
          ),
          "Outlet & Status"     => array(
              "title"     => "Outlet",
              "status"    => "Over Due",
              "data"      => $data_over_outlet,
              "status1"   => "Progress",
              "data1"     => $data_progress_outlet,
          ),
      );
      
      $c_i = 0;
foreach($array AS $title_tab => $data_export){
  $objPHPExcel->createSheet();
      $objPHPExcel->setActiveSheetIndex($c_i);
      $objPHPExcel->getActiveSheet()->setTitle($title_tab);
      
      $objPHPExcel->getActiveSheet()->mergeCells('A1:M1');
      $objPHPExcel->getActiveSheet()->setCellValue('A1', $title_tab);
      $objPHPExcel->getActiveSheet()->mergeCells('A2:M2');
//      $objPHPExcel->getActiveSheet()->setCellValue('A2', '(Problem Identification & Corrective Action)');
      $objPHPExcel->getActiveSheet()->getStyle('A1:M2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
      $objPHPExcel->getActiveSheet()->getStyle('A1:M2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
      $objPHPExcel->getActiveSheet()->getStyle('A1:M2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
      
//      $objPHPExcel->getActiveSheet()->setCellValue('A4', 'Outlet');
//      $objPHPExcel->getActiveSheet()->setCellValue('C4', "MR MONTIR");
      /*$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Tanggal');
      $objPHPExcel->getActiveSheet()->setCellValue('C5', $bulan[$detail[0]->month]." ".$detail[0]->year);
      $objPHPExcel->getActiveSheet()->setCellValue('A6', 'Bulan');
      $objPHPExcel->getActiveSheet()->setCellValue('A7', 'Minggu ke'); */
      
      $objPHPExcel->getActiveSheet()->getStyle('A1:M7')->getFont()->setBold(true);
//      $objPHPExcel->getActiveSheet()->getRowDimension(9)->setRowHeight(30);
//      $objPHPExcel->getActiveSheet()->getRowDimension(9)->setAutoSize(30);
      
      $objPHPExcel->getActiveSheet()->setCellValue('A7', $data_export['title']);
      $objPHPExcel->getActiveSheet()->getStyle("A7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('B7', $data_export['status']);
      $objPHPExcel->getActiveSheet()->getStyle("B7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $objPHPExcel->getActiveSheet()->setCellValue('C7', $data_export['status1']);
      $objPHPExcel->getActiveSheet()->getStyle("C7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      $objPHPExcel->getActiveSheet()->setCellValue('D7', "Progress + Over Due");
      $objPHPExcel->getActiveSheet()->getStyle("D7")->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      
      $r = 8;
      foreach($data_export['data'] AS $kdr => $data_rekap){
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$r, $data_rekap->name);
        $objPHPExcel->getActiveSheet()->getStyle('A'.$r)->applyFromArray(
            array(
              'alignment' => array(
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
              ),
              'borders' => array(
                'left'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
                'right'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
                'top'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
                'bottom'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
              ),
            )
        );
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$r, $data_rekap->pic_pica);
        $objPHPExcel->getActiveSheet()->getStyle('B'.$r)->applyFromArray(
            array(
              'alignment' => array(
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
              ),
              'borders' => array(
                'left'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
                'right'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
                'top'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
                'bottom'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
              ),
            )
        );
        
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$r, ($data_export['data1'][$kdr]->pic_pica - $data_rekap->pic_pica));
        $objPHPExcel->getActiveSheet()->getStyle('C'.$r)->applyFromArray(
            array(
              'alignment' => array(
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
              ),
              'borders' => array(
                'left'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
                'right'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
                'top'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
                'bottom'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
              ),
            )
        );
        
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$r, $data_export['data1'][$kdr]->pic_pica);
        $objPHPExcel->getActiveSheet()->getStyle('D'.$r)->applyFromArray(
            array(
              'alignment' => array(
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
              ),
              'borders' => array(
                'left'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
                'right'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_DOUBLE
                ),
                'top'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
                'bottom'     => array(
                  'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
              ),
            )
        );
        $r++;
      }
      
      $objPHPExcel->getActiveSheet()->setCellValue('A'.($r), "TOTAL");
      $objPHPExcel->getActiveSheet()->getStyle('A'.($r))->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      $objPHPExcel->getActiveSheet()->setCellValue('B'.$r, "=SUM(B8:B".($r-1).")");
      $objPHPExcel->getActiveSheet()->getStyle('B'.$r)->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      $objPHPExcel->getActiveSheet()->setCellValue('C'.$r, "=SUM(C8:C".($r-1).")");
      $objPHPExcel->getActiveSheet()->getStyle('C'.$r)->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      $objPHPExcel->getActiveSheet()->setCellValue('D'.$r, "=SUM(D8:D".($r-1).")");
      $objPHPExcel->getActiveSheet()->getStyle('D'.$r)->applyFromArray(
          array(
            'alignment' => array(
              'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
              'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
            ),
            'borders' => array(
              'left'     => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
              ),
              'right'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'top'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
              'bottom'     => array(
                'style' => PHPExcel_Style_Border::BORDER_DOUBLE
              ),
            ),
          )
      );
      $objPHPExcel->getActiveSheet()->getStyle('A'.$r.':D'.$r)->getFont()->setBold(true);
      
      $objPHPExcel->getActiveSheet()->getColumnDimension("A")->setAutoSize(true);
      $objPHPExcel->getActiveSheet()->getColumnDimension("B")->setAutoSize(true);
      $objPHPExcel->getActiveSheet()->getColumnDimension("C")->setAutoSize(true);
      $objPHPExcel->getActiveSheet()->getColumnDimension("D")->setAutoSize(true);
      
      $objPHPExcel->setActiveSheetIndex($c_i);
 $c_i++;     
}

      header('Content-Type: application/vnd.ms-excel');
      header('Content-Disposition: attachment;filename="'.$filename."-".date("Y m d").'"');
      header('Cache-Control: max-age=0');
      $objWriter = PHPExcel_IOFactory::createWriter($this->phpexcel, 'Excel5');
//$objWriter->save(str_replace('.php', '.xls', __FILE__));
      $objWriter->save('php://output');die;
      
    }
}
?>